{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ebe089ec-7b43-4714-bf97-91779512c7d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei'] # 用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus'] = False # 用来正常显示负号"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8b2c8ed2-914e-4765-9862-d7d3594b8aaf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>发货记录ID</th>\n",
       "      <th>发货记录完成时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>49870</td>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>49871</td>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>49872</td>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>49873</td>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>49874</td>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  发货记录ID             发货记录完成时间\n",
       "0               0    2509  2022-07-21 07:10:48\n",
       "1               1    2510  2022-07-21 07:19:48\n",
       "2               2    2514  2022-07-21 10:49:34\n",
       "3               3    2515  2022-07-21 10:49:35\n",
       "4               4    2516  2022-07-21 11:23:31\n",
       "...           ...     ...                  ...\n",
       "49870       49870   60712  2024-06-24 13:28:49\n",
       "49871       49871   60713  2024-06-24 14:08:54\n",
       "49872       49872   60714  2024-06-24 14:09:20\n",
       "49873       49873   60715  2024-06-24 14:40:16\n",
       "49874       49874   60716  2024-06-24 15:07:41\n",
       "\n",
       "[49875 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1=pd.read_excel(\"新.xlsx\")\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bc96df66-4a83-40e4-a8e5-133398392d78",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>客户下单条目ID</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>站点信息ID</th>\n",
       "      <th>产品规格字典值</th>\n",
       "      <th>产品品牌字典值</th>\n",
       "      <th>需求吨位</th>\n",
       "      <th>货运方式字典值</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>发货地址ID</th>\n",
       "      <th>到站时间</th>\n",
       "      <th>创建该订单的我司销售人员</th>\n",
       "      <th>名称</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>79</td>\n",
       "      <td>37</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-06-13 15:59:10</td>\n",
       "      <td>xssb</td>\n",
       "      <td>三部测试</td>\n",
       "      <td>NaN</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>745</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-06-29 05:46:42</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>865</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>200.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-01 04:30:19</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>928</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>200.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-02 10:46:07</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>958</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-03 04:27:59</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27948</th>\n",
       "      <td>27948</td>\n",
       "      <td>32911</td>\n",
       "      <td>388</td>\n",
       "      <td>1278</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>35.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>史丹</td>\n",
       "      <td>泽麟建材</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27949</th>\n",
       "      <td>27949</td>\n",
       "      <td>32912</td>\n",
       "      <td>636</td>\n",
       "      <td>1211</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>35.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>史丹</td>\n",
       "      <td>枣阳翔吉顺</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27950</th>\n",
       "      <td>27950</td>\n",
       "      <td>32913</td>\n",
       "      <td>497</td>\n",
       "      <td>1274</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>42.36</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-05 08:05:00</td>\n",
       "      <td>chediao</td>\n",
       "      <td>调度室</td>\n",
       "      <td>志扬云睿</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27951</th>\n",
       "      <td>27951</td>\n",
       "      <td>32914</td>\n",
       "      <td>479</td>\n",
       "      <td>793</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>甄常</td>\n",
       "      <td>荟聚源</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27952</th>\n",
       "      <td>27952</td>\n",
       "      <td>32915</td>\n",
       "      <td>546</td>\n",
       "      <td>979</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>105</td>\n",
       "      <td>2024-06-25 08:05:00</td>\n",
       "      <td>DS0403000</td>\n",
       "      <td>李庭辉</td>\n",
       "      <td>湖北玖伍建材</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27953 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  客户下单条目ID  客户信息ID  站点信息ID  产品规格字典值  产品品牌字典值    需求吨位  \\\n",
       "0               0        79      37      46        0        0  100.00   \n",
       "1               1       745     141     276        0        0  100.00   \n",
       "2               2       865     141     276        0        0  200.00   \n",
       "3               3       928     141     276        0        0  200.00   \n",
       "4               4       958     141     276        0        0  150.00   \n",
       "...           ...       ...     ...     ...      ...      ...     ...   \n",
       "27948       27948     32911     388    1278        1        9   35.00   \n",
       "27949       27949     32912     636    1211        1        9   35.00   \n",
       "27950       27950     32913     497    1274        1        9   42.36   \n",
       "27951       27951     32914     479     793        1        9   30.00   \n",
       "27952       27952     32915     546     979        1        9   30.00   \n",
       "\n",
       "       货运方式字典值         业务类型描述  发货地址ID                 到站时间 创建该订单的我司销售人员    名称  \\\n",
       "0            1  配送 (贸易+起驳+运输)     121  2022-06-13 15:59:10         xssb  三部测试   \n",
       "1            1  配送 (贸易+起驳+运输)     121  2022-06-29 05:46:42    DS0403001   何义奎   \n",
       "2            1  配送 (贸易+起驳+运输)     121  2022-07-01 04:30:19    DS0403001   何义奎   \n",
       "3            1  配送 (贸易+起驳+运输)     121  2022-07-02 10:46:07    DS0403001   何义奎   \n",
       "4            1  配送 (贸易+起驳+运输)     121  2022-07-03 04:27:59    DS0403001   何义奎   \n",
       "...        ...            ...     ...                  ...          ...   ...   \n",
       "27948        0        自提 (贸易)     104  2024-06-24 08:05:00    DS0403003    史丹   \n",
       "27949        0        自提 (贸易)     104  2024-06-24 08:05:00    DS0403003    史丹   \n",
       "27950        0        自提 (贸易)     104  2024-06-05 08:05:00      chediao   调度室   \n",
       "27951        0        自提 (贸易)     104  2024-06-24 08:05:00    DS0402003    甄常   \n",
       "27952        0     配送 (贸易+运输)     105  2024-06-25 08:05:00    DS0403000   李庭辉   \n",
       "\n",
       "         客户名称 发货地名称 货品类型  \n",
       "0         NaN  江南码头   水泥  \n",
       "1         盛华鑫  江南码头   水泥  \n",
       "2         盛华鑫  江南码头   水泥  \n",
       "3         盛华鑫  江南码头   水泥  \n",
       "4         盛华鑫  江南码头   水泥  \n",
       "...       ...   ...  ...  \n",
       "27948    泽麟建材  武新一期   矿粉  \n",
       "27949   枣阳翔吉顺  武新一期   矿粉  \n",
       "27950    志扬云睿  武新一期   矿粉  \n",
       "27951     荟聚源  武新一期   矿粉  \n",
       "27952  湖北玖伍建材  武新二期   矿粉  \n",
       "\n",
       "[27953 rows x 16 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2=pd.read_excel(\"6.28_df2.xlsx\")\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5d5bd1a7-af2f-4870-a7a6-792ad4e6acea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>产品规格字典值</th>\n",
       "      <th>产品规格</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>V800(袋装)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>V800(散装)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>PO52.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   产品规格字典值      产品规格\n",
       "0        0   P.O42.5\n",
       "1        1       S95\n",
       "2        2  V800(袋装)\n",
       "3        3  V800(散装)\n",
       "4        4    PO52.5"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3=pd.read_excel(\"copy.xlsx\")\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0bc2c8ae-8671-441f-9689-697506418f7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>客户下单条目ID</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>站点信息ID</th>\n",
       "      <th>产品规格字典值</th>\n",
       "      <th>产品品牌字典值</th>\n",
       "      <th>需求吨位</th>\n",
       "      <th>货运方式字典值</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>发货地址ID</th>\n",
       "      <th>到站时间</th>\n",
       "      <th>创建该订单的我司销售人员</th>\n",
       "      <th>名称</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>79</td>\n",
       "      <td>37</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-06-13 15:59:10</td>\n",
       "      <td>xssb</td>\n",
       "      <td>三部测试</td>\n",
       "      <td>NaN</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>745</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-06-29 05:46:42</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>865</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>200.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-01 04:30:19</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>928</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>200.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-02 10:46:07</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>958</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-03 04:27:59</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27948</th>\n",
       "      <td>27948</td>\n",
       "      <td>32911</td>\n",
       "      <td>388</td>\n",
       "      <td>1278</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>35.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>史丹</td>\n",
       "      <td>泽麟建材</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27949</th>\n",
       "      <td>27949</td>\n",
       "      <td>32912</td>\n",
       "      <td>636</td>\n",
       "      <td>1211</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>35.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>史丹</td>\n",
       "      <td>枣阳翔吉顺</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27950</th>\n",
       "      <td>27950</td>\n",
       "      <td>32913</td>\n",
       "      <td>497</td>\n",
       "      <td>1274</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>42.36</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-05 08:05:00</td>\n",
       "      <td>chediao</td>\n",
       "      <td>调度室</td>\n",
       "      <td>志扬云睿</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27951</th>\n",
       "      <td>27951</td>\n",
       "      <td>32914</td>\n",
       "      <td>479</td>\n",
       "      <td>793</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>甄常</td>\n",
       "      <td>荟聚源</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27952</th>\n",
       "      <td>27952</td>\n",
       "      <td>32915</td>\n",
       "      <td>546</td>\n",
       "      <td>979</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>105</td>\n",
       "      <td>2024-06-25 08:05:00</td>\n",
       "      <td>DS0403000</td>\n",
       "      <td>李庭辉</td>\n",
       "      <td>湖北玖伍建材</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27953 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  客户下单条目ID  客户信息ID  站点信息ID  产品规格字典值  产品品牌字典值    需求吨位  \\\n",
       "0               0        79      37      46        0        0  100.00   \n",
       "1               1       745     141     276        0        0  100.00   \n",
       "2               2       865     141     276        0        0  200.00   \n",
       "3               3       928     141     276        0        0  200.00   \n",
       "4               4       958     141     276        0        0  150.00   \n",
       "...           ...       ...     ...     ...      ...      ...     ...   \n",
       "27948       27948     32911     388    1278        1        9   35.00   \n",
       "27949       27949     32912     636    1211        1        9   35.00   \n",
       "27950       27950     32913     497    1274        1        9   42.36   \n",
       "27951       27951     32914     479     793        1        9   30.00   \n",
       "27952       27952     32915     546     979        1        9   30.00   \n",
       "\n",
       "       货运方式字典值         业务类型描述  发货地址ID                 到站时间 创建该订单的我司销售人员    名称  \\\n",
       "0            1  配送 (贸易+起驳+运输)     121  2022-06-13 15:59:10         xssb  三部测试   \n",
       "1            1  配送 (贸易+起驳+运输)     121  2022-06-29 05:46:42    DS0403001   何义奎   \n",
       "2            1  配送 (贸易+起驳+运输)     121  2022-07-01 04:30:19    DS0403001   何义奎   \n",
       "3            1  配送 (贸易+起驳+运输)     121  2022-07-02 10:46:07    DS0403001   何义奎   \n",
       "4            1  配送 (贸易+起驳+运输)     121  2022-07-03 04:27:59    DS0403001   何义奎   \n",
       "...        ...            ...     ...                  ...          ...   ...   \n",
       "27948        0        自提 (贸易)     104  2024-06-24 08:05:00    DS0403003    史丹   \n",
       "27949        0        自提 (贸易)     104  2024-06-24 08:05:00    DS0403003    史丹   \n",
       "27950        0        自提 (贸易)     104  2024-06-05 08:05:00      chediao   调度室   \n",
       "27951        0        自提 (贸易)     104  2024-06-24 08:05:00    DS0402003    甄常   \n",
       "27952        0     配送 (贸易+运输)     105  2024-06-25 08:05:00    DS0403000   李庭辉   \n",
       "\n",
       "         客户名称 发货地名称 货品类型     产品规格  \n",
       "0         NaN  江南码头   水泥  P.O42.5  \n",
       "1         盛华鑫  江南码头   水泥  P.O42.5  \n",
       "2         盛华鑫  江南码头   水泥  P.O42.5  \n",
       "3         盛华鑫  江南码头   水泥  P.O42.5  \n",
       "4         盛华鑫  江南码头   水泥  P.O42.5  \n",
       "...       ...   ...  ...      ...  \n",
       "27948    泽麟建材  武新一期   矿粉      S95  \n",
       "27949   枣阳翔吉顺  武新一期   矿粉      S95  \n",
       "27950    志扬云睿  武新一期   矿粉      S95  \n",
       "27951     荟聚源  武新一期   矿粉      S95  \n",
       "27952  湖北玖伍建材  武新二期   矿粉      S95  \n",
       "\n",
       "[27953 rows x 17 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df2=pd.merge(df2, df3[[\"产品规格字典值\",\"产品规格\"]], on=\"产品规格字典值\", how='left')\n",
    "merged_df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "476e1da8-624d-45a2-a966-f0de4f317f74",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>客户下单条目ID</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>站点信息ID</th>\n",
       "      <th>产品品牌字典值</th>\n",
       "      <th>需求吨位</th>\n",
       "      <th>货运方式字典值</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>发货地址ID</th>\n",
       "      <th>到站时间</th>\n",
       "      <th>创建该订单的我司销售人员</th>\n",
       "      <th>名称</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>79</td>\n",
       "      <td>37</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-06-13 15:59:10</td>\n",
       "      <td>xssb</td>\n",
       "      <td>三部测试</td>\n",
       "      <td>NaN</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>745</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-06-29 05:46:42</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>865</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>200.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-01 04:30:19</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>928</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>200.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-02 10:46:07</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>958</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>150.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-03 04:27:59</td>\n",
       "      <td>DS0403001</td>\n",
       "      <td>何义奎</td>\n",
       "      <td>盛华鑫</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27948</th>\n",
       "      <td>27948</td>\n",
       "      <td>32911</td>\n",
       "      <td>388</td>\n",
       "      <td>1278</td>\n",
       "      <td>9</td>\n",
       "      <td>35.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>史丹</td>\n",
       "      <td>泽麟建材</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27949</th>\n",
       "      <td>27949</td>\n",
       "      <td>32912</td>\n",
       "      <td>636</td>\n",
       "      <td>1211</td>\n",
       "      <td>9</td>\n",
       "      <td>35.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>史丹</td>\n",
       "      <td>枣阳翔吉顺</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27950</th>\n",
       "      <td>27950</td>\n",
       "      <td>32913</td>\n",
       "      <td>497</td>\n",
       "      <td>1274</td>\n",
       "      <td>9</td>\n",
       "      <td>42.36</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-05 08:05:00</td>\n",
       "      <td>chediao</td>\n",
       "      <td>调度室</td>\n",
       "      <td>志扬云睿</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27951</th>\n",
       "      <td>27951</td>\n",
       "      <td>32914</td>\n",
       "      <td>479</td>\n",
       "      <td>793</td>\n",
       "      <td>9</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>甄常</td>\n",
       "      <td>荟聚源</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27952</th>\n",
       "      <td>27952</td>\n",
       "      <td>32915</td>\n",
       "      <td>546</td>\n",
       "      <td>979</td>\n",
       "      <td>9</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>105</td>\n",
       "      <td>2024-06-25 08:05:00</td>\n",
       "      <td>DS0403000</td>\n",
       "      <td>李庭辉</td>\n",
       "      <td>湖北玖伍建材</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27953 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  客户下单条目ID  客户信息ID  站点信息ID  产品品牌字典值    需求吨位  货运方式字典值  \\\n",
       "0               0        79      37      46        0  100.00        1   \n",
       "1               1       745     141     276        0  100.00        1   \n",
       "2               2       865     141     276        0  200.00        1   \n",
       "3               3       928     141     276        0  200.00        1   \n",
       "4               4       958     141     276        0  150.00        1   \n",
       "...           ...       ...     ...     ...      ...     ...      ...   \n",
       "27948       27948     32911     388    1278        9   35.00        0   \n",
       "27949       27949     32912     636    1211        9   35.00        0   \n",
       "27950       27950     32913     497    1274        9   42.36        0   \n",
       "27951       27951     32914     479     793        9   30.00        0   \n",
       "27952       27952     32915     546     979        9   30.00        0   \n",
       "\n",
       "              业务类型描述  发货地址ID                 到站时间 创建该订单的我司销售人员    名称    客户名称  \\\n",
       "0      配送 (贸易+起驳+运输)     121  2022-06-13 15:59:10         xssb  三部测试     NaN   \n",
       "1      配送 (贸易+起驳+运输)     121  2022-06-29 05:46:42    DS0403001   何义奎     盛华鑫   \n",
       "2      配送 (贸易+起驳+运输)     121  2022-07-01 04:30:19    DS0403001   何义奎     盛华鑫   \n",
       "3      配送 (贸易+起驳+运输)     121  2022-07-02 10:46:07    DS0403001   何义奎     盛华鑫   \n",
       "4      配送 (贸易+起驳+运输)     121  2022-07-03 04:27:59    DS0403001   何义奎     盛华鑫   \n",
       "...              ...     ...                  ...          ...   ...     ...   \n",
       "27948        自提 (贸易)     104  2024-06-24 08:05:00    DS0403003    史丹    泽麟建材   \n",
       "27949        自提 (贸易)     104  2024-06-24 08:05:00    DS0403003    史丹   枣阳翔吉顺   \n",
       "27950        自提 (贸易)     104  2024-06-05 08:05:00      chediao   调度室    志扬云睿   \n",
       "27951        自提 (贸易)     104  2024-06-24 08:05:00    DS0402003    甄常     荟聚源   \n",
       "27952     配送 (贸易+运输)     105  2024-06-25 08:05:00    DS0403000   李庭辉  湖北玖伍建材   \n",
       "\n",
       "      发货地名称 货品类型     产品规格  \n",
       "0      江南码头   水泥  P.O42.5  \n",
       "1      江南码头   水泥  P.O42.5  \n",
       "2      江南码头   水泥  P.O42.5  \n",
       "3      江南码头   水泥  P.O42.5  \n",
       "4      江南码头   水泥  P.O42.5  \n",
       "...     ...  ...      ...  \n",
       "27948  武新一期   矿粉      S95  \n",
       "27949  武新一期   矿粉      S95  \n",
       "27950  武新一期   矿粉      S95  \n",
       "27951  武新一期   矿粉      S95  \n",
       "27952  武新二期   矿粉      S95  \n",
       "\n",
       "[27953 rows x 16 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df2=merged_df2.drop(columns=\"产品规格字典值\")\n",
    "merged_df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "12fc822a-2fea-479a-9c8d-dd529a163595",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df2.to_excel(\"cpgg.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c022f4e0-ca45-4c27-847e-bb91a411179b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>dict_type</th>\n",
       "      <th>dict_value</th>\n",
       "      <th>dict_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>sys_user_sex</td>\n",
       "      <td>0</td>\n",
       "      <td>男</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>sys_user_sex</td>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>sys_user_sex</td>\n",
       "      <td>2</td>\n",
       "      <td>未知</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>sys_show_hide</td>\n",
       "      <td>0</td>\n",
       "      <td>显示</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>sys_show_hide</td>\n",
       "      <td>1</td>\n",
       "      <td>隐藏</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>745</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>0</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>746</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>1</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>747</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>2</td>\n",
       "      <td>V800(袋装)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264</th>\n",
       "      <td>748</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>3</td>\n",
       "      <td>V800(散装)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>749</td>\n",
       "      <td>ERP_CPGG</td>\n",
       "      <td>4</td>\n",
       "      <td>PO52.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>266 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id      dict_type dict_value dict_label\n",
       "0      1   sys_user_sex          0          男\n",
       "1      2   sys_user_sex          1          女\n",
       "2      3   sys_user_sex          2         未知\n",
       "3      4  sys_show_hide          0         显示\n",
       "4      5  sys_show_hide          1         隐藏\n",
       "..   ...            ...        ...        ...\n",
       "261  745       ERP_CPGG          0    P.O42.5\n",
       "262  746       ERP_CPGG          1        S95\n",
       "263  747       ERP_CPGG          2   V800(袋装)\n",
       "264  748       ERP_CPGG          3   V800(散装)\n",
       "265  749       ERP_CPGG          4     PO52.5\n",
       "\n",
       "[266 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4=pd.read_csv(\"数据/系统字典表.csv\")\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "23b9cfe3-82bd-4a64-bca4-ca4b997f8f93",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dict_value</th>\n",
       "      <th>dict_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>0</td>\n",
       "      <td>海螺水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>1</td>\n",
       "      <td>东方希望水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>2</td>\n",
       "      <td>三峡水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>3</td>\n",
       "      <td>京兰水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>4</td>\n",
       "      <td>四棵松水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>5</td>\n",
       "      <td>娲石水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>6</td>\n",
       "      <td>福兴水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>7</td>\n",
       "      <td>华新水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>8</td>\n",
       "      <td>金盛兰矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>9</td>\n",
       "      <td>武新矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>10</td>\n",
       "      <td>鑫缘矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>11</td>\n",
       "      <td>钢华矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>12</td>\n",
       "      <td>嘉华矿粉</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    dict_value dict_label\n",
       "215          0       海螺水泥\n",
       "216          1     东方希望水泥\n",
       "217          2       三峡水泥\n",
       "218          3       京兰水泥\n",
       "219          4      四棵松水泥\n",
       "220          5       娲石水泥\n",
       "221          6       福兴水泥\n",
       "222          7       华新水泥\n",
       "223          8      金盛兰矿粉\n",
       "224          9       武新矿粉\n",
       "225         10       鑫缘矿粉\n",
       "226         11       钢华矿粉\n",
       "227         12       嘉华矿粉"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_erp_only = df4[df4['dict_type'] == 'ERP_CPPP']\n",
    "df_erp_only=df_erp_only.drop(columns=[\"id\",\"dict_type\"])\n",
    "df_erp_only"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1ff2f367-1f51-4c20-ac3a-cfbc71ed1cde",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>产品品牌字典值</th>\n",
       "      <th>产品品牌</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>0</td>\n",
       "      <td>海螺水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>1</td>\n",
       "      <td>东方希望水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>2</td>\n",
       "      <td>三峡水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>3</td>\n",
       "      <td>京兰水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>4</td>\n",
       "      <td>四棵松水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>5</td>\n",
       "      <td>娲石水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>6</td>\n",
       "      <td>福兴水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>7</td>\n",
       "      <td>华新水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>8</td>\n",
       "      <td>金盛兰矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>9</td>\n",
       "      <td>武新矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>10</td>\n",
       "      <td>鑫缘矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>11</td>\n",
       "      <td>钢华矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>12</td>\n",
       "      <td>嘉华矿粉</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    产品品牌字典值    产品品牌\n",
       "215       0    海螺水泥\n",
       "216       1  东方希望水泥\n",
       "217       2    三峡水泥\n",
       "218       3    京兰水泥\n",
       "219       4   四棵松水泥\n",
       "220       5    娲石水泥\n",
       "221       6    福兴水泥\n",
       "222       7    华新水泥\n",
       "223       8   金盛兰矿粉\n",
       "224       9    武新矿粉\n",
       "225      10    鑫缘矿粉\n",
       "226      11    钢华矿粉\n",
       "227      12    嘉华矿粉"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_erp_only.columns=[\"产品品牌字典值\",\"产品品牌\"]\n",
    "df_erp_only"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85fd950c-7c6e-4ec0-a4a3-2a11b495ffcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_erp_only.to_excel(\"cppp\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "661a1f84-75bc-446c-8e24-0d532c736bf5",
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "You are trying to merge on int64 and object columns for key '产品品牌字典值'. If you wish to proceed you should use pd.concat",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[10], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m merged1_df2\u001b[38;5;241m=\u001b[39mpd\u001b[38;5;241m.\u001b[39mmerge(merged_df2, df_erp_only[[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m产品品牌字典值\u001b[39m\u001b[38;5;124m\"\u001b[39m,\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m产品品牌\u001b[39m\u001b[38;5;124m\"\u001b[39m]], on\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m产品品牌字典值\u001b[39m\u001b[38;5;124m\"\u001b[39m, how\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mleft\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m      2\u001b[0m merged1_df2\u001b[38;5;241m=\u001b[39mmerged1_df2\u001b[38;5;241m.\u001b[39mdrop(columns\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m产品品牌字典值\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m      3\u001b[0m merged1_df2\n",
      "File \u001b[1;32mD:\\Anaconda\\Lib\\site-packages\\pandas\\core\\reshape\\merge.py:169\u001b[0m, in \u001b[0;36mmerge\u001b[1;34m(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)\u001b[0m\n\u001b[0;32m    154\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m _cross_merge(\n\u001b[0;32m    155\u001b[0m         left_df,\n\u001b[0;32m    156\u001b[0m         right_df,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m    166\u001b[0m         copy\u001b[38;5;241m=\u001b[39mcopy,\n\u001b[0;32m    167\u001b[0m     )\n\u001b[0;32m    168\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m--> 169\u001b[0m     op \u001b[38;5;241m=\u001b[39m _MergeOperation(\n\u001b[0;32m    170\u001b[0m         left_df,\n\u001b[0;32m    171\u001b[0m         right_df,\n\u001b[0;32m    172\u001b[0m         how\u001b[38;5;241m=\u001b[39mhow,\n\u001b[0;32m    173\u001b[0m         on\u001b[38;5;241m=\u001b[39mon,\n\u001b[0;32m    174\u001b[0m         left_on\u001b[38;5;241m=\u001b[39mleft_on,\n\u001b[0;32m    175\u001b[0m         right_on\u001b[38;5;241m=\u001b[39mright_on,\n\u001b[0;32m    176\u001b[0m         left_index\u001b[38;5;241m=\u001b[39mleft_index,\n\u001b[0;32m    177\u001b[0m         right_index\u001b[38;5;241m=\u001b[39mright_index,\n\u001b[0;32m    178\u001b[0m         sort\u001b[38;5;241m=\u001b[39msort,\n\u001b[0;32m    179\u001b[0m         suffixes\u001b[38;5;241m=\u001b[39msuffixes,\n\u001b[0;32m    180\u001b[0m         indicator\u001b[38;5;241m=\u001b[39mindicator,\n\u001b[0;32m    181\u001b[0m         validate\u001b[38;5;241m=\u001b[39mvalidate,\n\u001b[0;32m    182\u001b[0m     )\n\u001b[0;32m    183\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m op\u001b[38;5;241m.\u001b[39mget_result(copy\u001b[38;5;241m=\u001b[39mcopy)\n",
      "File \u001b[1;32mD:\\Anaconda\\Lib\\site-packages\\pandas\\core\\reshape\\merge.py:804\u001b[0m, in \u001b[0;36m_MergeOperation.__init__\u001b[1;34m(self, left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, indicator, validate)\u001b[0m\n\u001b[0;32m    800\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_validate_tolerance(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mleft_join_keys)\n\u001b[0;32m    802\u001b[0m \u001b[38;5;66;03m# validate the merge keys dtypes. We may need to coerce\u001b[39;00m\n\u001b[0;32m    803\u001b[0m \u001b[38;5;66;03m# to avoid incompatible dtypes\u001b[39;00m\n\u001b[1;32m--> 804\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_maybe_coerce_merge_keys()\n\u001b[0;32m    806\u001b[0m \u001b[38;5;66;03m# If argument passed to validate,\u001b[39;00m\n\u001b[0;32m    807\u001b[0m \u001b[38;5;66;03m# check if columns specified as unique\u001b[39;00m\n\u001b[0;32m    808\u001b[0m \u001b[38;5;66;03m# are in fact unique.\u001b[39;00m\n\u001b[0;32m    809\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m validate \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n",
      "File \u001b[1;32mD:\\Anaconda\\Lib\\site-packages\\pandas\\core\\reshape\\merge.py:1479\u001b[0m, in \u001b[0;36m_MergeOperation._maybe_coerce_merge_keys\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1473\u001b[0m     \u001b[38;5;66;03m# unless we are merging non-string-like with string-like\u001b[39;00m\n\u001b[0;32m   1474\u001b[0m     \u001b[38;5;28;01melif\u001b[39;00m (\n\u001b[0;32m   1475\u001b[0m         inferred_left \u001b[38;5;129;01min\u001b[39;00m string_types \u001b[38;5;129;01mand\u001b[39;00m inferred_right \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;129;01min\u001b[39;00m string_types\n\u001b[0;32m   1476\u001b[0m     ) \u001b[38;5;129;01mor\u001b[39;00m (\n\u001b[0;32m   1477\u001b[0m         inferred_right \u001b[38;5;129;01min\u001b[39;00m string_types \u001b[38;5;129;01mand\u001b[39;00m inferred_left \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;129;01min\u001b[39;00m string_types\n\u001b[0;32m   1478\u001b[0m     ):\n\u001b[1;32m-> 1479\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(msg)\n\u001b[0;32m   1481\u001b[0m \u001b[38;5;66;03m# datetimelikes must match exactly\u001b[39;00m\n\u001b[0;32m   1482\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m needs_i8_conversion(lk\u001b[38;5;241m.\u001b[39mdtype) \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m needs_i8_conversion(rk\u001b[38;5;241m.\u001b[39mdtype):\n",
      "\u001b[1;31mValueError\u001b[0m: You are trying to merge on int64 and object columns for key '产品品牌字典值'. If you wish to proceed you should use pd.concat"
     ]
    }
   ],
   "source": [
    "merged1_df2=pd.merge(merged_df2, df_erp_only[[\"产品品牌字典值\",\"产品品牌\"]], on=\"产品品牌字典值\", how='left')\n",
    "merged1_df2=merged1_df2.drop(columns=\"产品品牌字典值\")\n",
    "merged1_df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a689fd1-1374-47d2-a3b1-7de24ff84072",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
